﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Data;
using System.Data.OleDb;
using GemBox.ExcelLite;
namespace iMall.AtoZ.Helper
{
    /// <summary>   
    /// Excel 操作类    
    /// 采用GemBox.ExcelLite第三方控件  
    /// </summary>
    public class GemBoxExcelLiteHelper
    {

        public GemBoxExcelLiteHelper() { }

        #region 在线生成Excel
        /// <summary>
        /// 在线生成Excel
        /// </summary>
        /// <param name="fileName">文件名称 注:必须加这个方法
        /// public override void VerifyRenderingInServerForm(Control control)
        /// {
        /// Confirms that an HtmlForm control is rendered for
        /// }
        /// </param>
        /// <param name="page"></param>
        /// <param name="gridView"></param>
        public static void OnlineSaveExcel(string fileName, Page page, System.Web.UI.WebControls.GridView gridView)
        {
            page.Response.Clear();
            page.Response.AddHeader("content-disposition", "attachment;filename=" + fileName);
            page.Response.Charset = "gb2312";
            page.Response.ContentType = "application/vnd.xls";
            using (System.IO.StringWriter stringWrite = new System.IO.StringWriter())
            {
                System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
                //先设置分页为false
                gridView.AllowPaging = false;
                gridView.RenderControl(htmlWrite);
                page.Response.Write(stringWrite.ToString());
            }
            page.Response.End();
        }
        #endregion
        #region  生成Excel
        /// <summary>
        /// 生成Excel
        /// </summary>
        /// <param name="path">绝对路径</param>
        /// <param name="page"></param>
        /// <param name="isDownload">是否提供下载,true是,false否</param>
        /// <param name="isDelete">是否删除本地生成的Excel,true是,false否</param>
        /// <param name="titles">Excel标题</param>
        /// <param name="gridView">数据</param>
        public static void SaveExcel(string path, System.Web.UI.Page page, bool isDownload, bool isDelete, System.Web.UI.WebControls.GridView gridView)
        {
            try
            {
                //保存在本地
                SaveToXls(path, gridView);
                if (isDownload)
                {
                    //提供下载
                    UploadExcel(path, page, isDelete);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region 生成Excel
        /// <summary>
        /// 生成Excel
        /// </summary>
        /// <param name="path">绝对路径</param>
        /// <param name="page"></param>
        /// <param name="isDownload">是否提供下载,true是,false否</param>
        /// <param name="isDelete">是否删除本地生成的Excel,true是,false否</param>
        /// <param name="titles">Excel标题</param>
        /// <param name="ds">数据</param>
        public static void SaveExcel(string path, Page page, bool isDownload, bool isDelete, IList<String> titles, DataSet ds)
        {
            try
            {
                //保存在本地
                SaveToXls(path, titles, ds);
                if (isDownload)
                {
                    //提供下载
                    UploadExcel(path, page, isDelete);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
        #region 提供下载
        /// <summary>
        /// 提供下载
        /// </summary>
        /// <param name="path"></param>
        /// <param name="page"></param>
        ///  <param name="isDelete"></param>
        private static void UploadExcel(string path, Page page, bool isDelete)
        {
            System.IO.FileInfo file = new System.IO.FileInfo(path);
            page.Response.Clear();
            page.Response.Charset = "GB2312";
            page.Response.ContentEncoding = System.Text.Encoding.UTF8;
            // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 
            page.Response.AddHeader("Content-Disposition", "attachment; filename=" + page.Server.UrlEncode(file.Name));
            // 添加头信息，指定文件大小，让浏览器能够显示下载进度 
            page.Response.AddHeader("Content-Length", file.Length.ToString());
            // 指定返回的是一个不能被客户端读取的流，必须被下载 
            page.Response.ContentType = "application/ms-excel";
            // 把文件流发送到客户端 
            page.Response.WriteFile(file.FullName);
            page.Response.Flush();
            if (isDelete)
            {
                System.IO.File.Delete(path);
            }
            // 停止页面的执行 
            page.Response.End();
        }
        #endregion
        #region 保存在本地
        /// <summary>
        /// 保存在本地
        /// </summary>
        /// <param name="path">绝对路径</param>
        /// <param name="gridView">数据</param>
        private static void SaveToXls(string path, System.Web.UI.WebControls.GridView gridView)
        {
            ExcelFile excelFile = new ExcelFile();
            ExcelWorksheet sheet = excelFile.Worksheets.Add("Sheet1");
            int columns = gridView.HeaderRow.Cells.Count;
            int rows = gridView.Rows.Count;
            for (int j = 0; j < columns; j++)
            {
                sheet.Cells[0, j].Value = gridView.HeaderRow.Cells[j].Text;
                //修改Excel表头字体，现在为黑体12号字体
                sheet.Cells[0, j].Style.Font.Size = 240;
                sheet.Cells[0, j].Style.Font.Name = "黑体";
            }
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    sheet.Cells[i + 1, j].Value = gridView.Rows[i].Cells[j].Text;
                }
            }
            excelFile.SaveXls(path);
        }

        //
        //网上源码
        //
        //private static void SaveToXls(string path, System.Web.UI.WebControls.GridView gridView)
        //{
        //    ExcelFile excelFile = new ExcelFile();
        //    ExcelWorksheet sheet = excelFile.Worksheets.Add("Sheet1");
        //    int columns = gridView.Columns.Count;
        //    int rows = gridView.Rows.Count;
        //    for (int j = 0; j < columns; j++)
        //    {
        //        sheet.Cells[0, j].Value = gridView.Columns[j].HeaderText;
        //    }
        //    for (int i = 0; i < rows; i++)
        //    {
        //        for (int j = 0; j < columns; j++)
        //        {
        //            sheet.Cells[i + 1, j].Value = gridView.Rows[i].Cells[j].Text;
        //        }
        //    }
        //    excelFile.SaveXls(path);
        //}
        #endregion
        #region 保存在本地
        /// <summary>
        /// 保存在本地
        /// </summary>
        /// <param name="path">绝对路径</param>
        /// <param name="titles">Excel标题</param>
        /// <param name="ds">数据</param>
        private static void SaveToXls(string path, IList<String> titles, DataSet ds)
        {
            DataTable dt = ds.Tables[0];
            ExcelFile excelFile = new ExcelFile();
            ExcelWorksheet sheet = excelFile.Worksheets.Add("Sheet1");
            int columns = dt.Columns.Count;
            int rows = dt.Rows.Count;
            int index = 0;
            if (titles != null && titles.Count > 0)
            {
                for (int i = 0; i < titles.Count; i++)
                {
                    sheet.Cells[0, i].Value = titles[i];
                }
                index++;
            }
            for (int i = 0; i < rows; i++)
            {
                for (int j = 0; j < columns; j++)
                {
                    sheet.Cells[i + index, j].Value = dt.Rows[i][j].ToString();
                }
            }
            excelFile.SaveXls(path);
        }
        #endregion

        #region 从Excel导入DataSet
        /// <summary>
        /// 从Excel导入DataSet
        /// </summary>
        /// <param name="path">路径</param>
        /// <param name="sheetName">sheetName</param>
        /// <param name="hDRString">第一列是否为列名</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        public static DataSet LoadExcel(string path, string sheetName, HDRString hDRString, string tableName)
        {
            //HDR和IMEX也一定要配合使用,IMEX=1应该是将所有的列全部视为文本,HDR:YES第一行作为列名，NO表示第一行不为列名
            string connectionString = String.Format("Provider=Microsoft.Ace.OleDb.12.0;data source={0};Extended Properties='Excel 12.0; HDR={1}; IMEX=1'", path, (hDRString == HDRString.YES ? "YES" : "NO"));
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                OleDbDataAdapter odda = new OleDbDataAdapter(String.Format("select * from [{0}$]", sheetName), conn);
                DataSet ds = new DataSet();
                odda.Fill(ds, tableName);
                odda.Dispose();
                conn.Close();
                conn.Dispose();
                return ds;
            }
        }
        #endregion


        public static List<string> GetExcelSheetNames(string path)
        {
            List<string> names = new List<string>();
            string connectionString = String.Format("Provider=Microsoft.Ace.OleDb.12.0;data source={0};Extended Properties='Excel 12.0; HDR=NO; IMEX=1'", path);
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }

                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }

                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                foreach (DataRow dr in dt.Rows)
                {
                    names.Add(dr[2].ToString().Replace("$", "").Replace("'", ""));
                }
                conn.Close();
                conn.Dispose();
            }

            return names;
        }
    }

    public enum HDRString
    {
        YES,
        NO,
    }
}
